import os
from datetime import datetime
import json
from flask import make_response, redirect, render_template, jsonify, send_from_directory, session, request, url_for,flash
#execute('scrapy crawl home -s JOBDIR=crawls/csdn'.split())
from flask import Flask
from flask_cache import Cache
from common import *

app=Flask(__name__)
app.jinja_env.variable_start_string = '<< '
app.jinja_env.variable_end_string = ' >>'
app.jinja_env.block_start_string='<%'
app.jinja_env.block_end_string = '%>'
app.jinja_env.comment_start_string = '<#'
app.jinja_env.comment_end_string = '#>'
cache = Cache(app,config={'CACHE_TYPE': 'redis','CACHE_REDIS_PORT':9211,'CACHE_REDIS_DB':'8','CACHE_REDIS_PASSWORD':'FanTan879425','CACHE_REDIS_HOST':'redis.fzj.com'})

@app.route('/',methods=['GET'])
@cache.cached(timeout=60*60*24)
def index():
    conn, cursor = connect()
    # 数据采集每日统计
    execute(conn, cursor,
            'select date(create_time) as create_time,count(*) count from csdn_blog group by date(create_time)')
    #CsdnBlog.query(extract('date', CsdnBlog.create_time), func.count('*')).group_by(extract('date', CsdnBlog.create_time)).all()
    result = cursor.fetchall()
    bdne_data={datetime.strftime(item['create_time'],'%Y-%m-%d'):item['count'] for item in result}
    blog_data_number_by_everyday_data=list(bdne_data.values())
    blog_data_number_by_everyday_time=list(bdne_data.keys())
    # 数据时间分布统计（年）
    execute(conn, cursor,
            'select year(time) as year,count(*) count from csdn_blog where time is not null group by year(time)')
    result = cursor.fetchall()
    bdnby_data = {item['year']: item['count'] for item in result}
    blog_data_number_by_year_data = [{'value': v, 'name':k} for k,v in bdnby_data.items() if k]
    # 数据时间分布统计（月）
    execute(conn, cursor,
            'select month(time) as month,count(*) count from csdn_blog where time is not null group by month(time)')
    result = cursor.fetchall()
    bdnbm_data = {item['month']: item['count'] for item in result}
    blog_data_number_by_month_data = [{'value': v, 'name':k} for k,v in bdnbm_data.items() if k]
    # 数据时间分布统计（年-月）
    execute(conn, cursor,
            "select concat(year(time),'-',month(time)) as time,count(*) count from csdn_blog where time is not null group by concat(year(time),'-',month(time)) order by concat(year(time),'-',month(time))")
    result = cursor.fetchall()
    bdnbyam_data = {item['time']: item['count'] for item in result}
    blog_data_number_by_year_and_month_data = list(bdnbyam_data.values())
    blog_data_number_by_year_and_month_time = list(bdnbyam_data.keys())
    connect_close(conn, cursor)
    monitor=getMemCpu()
    return render_template('index.html',bdned=blog_data_number_by_everyday_data,bdnet=blog_data_number_by_everyday_time,bdnbyd=blog_data_number_by_year_data,bdnbmd=blog_data_number_by_month_data,bdnbyamt=blog_data_number_by_year_and_month_time,bdnbyamd=blog_data_number_by_year_and_month_data,monitor=monitor,title='首页')



@app.route('/system')
def system():
    return jsonify(getMemCpu())


@app.route('/list',methods=['GET','POST'])
@cache.cached(timeout=60*60*24*100,key_prefix=make_cache_key)
def datalist():
    '''
    首页
    :return:
    '''
    if request.method == 'POST':
        page = int(request.form.get('page'))
        limit = int(request.form.get('limit'))
        conn, cursor = connect()
        execute(conn,cursor, 'select count(*) count from csdn_blog')
        blogCount=cursor.fetchone()['count']
        execute(conn, cursor, 'select * from csdn_blog limit {},{}'.format((page-1)*limit,limit))
        blogs=cursor.fetchall()
        returnData = {}
        returnData['code'] = 0
        returnData['count'] = blogCount
        returnData['data'] = [{'Id': item['Id'], 'title': item['title'],'href':item['href'],'nickName':item['nickName'],'time':datetime.strftime(item['time'],'%Y-%m-%d %H:%M:%S') if item['time'] else '','tag':item['tag']} for item in blogs]
        returnData['msg'] = ''
        connect_close(conn, cursor)
        return jsonify(returnData)
    return render_template('list.html', title='文章列表')

@app.route('/edit/blog',methods=['POST'])
def editBlog():
    returnData={'status':0,'msg':''}
    try:
        Id=request.form.get('Id',0)
        if Id:
            field=request.form.get('field')
            value=request.form.get('value')
            sql=f'update csdn_blog set {field}=%s where Id={Id}'
            conn, cursor = connect()
            res=execute(conn, cursor, sql,(value,))
            if res:
                returnData['status']=1
            else:
                returnData['msg'] = '未修改'
        else:
            returnData['msg'] = 'Id没有提交'
        connect_close(conn, cursor)
    except Exception as e:
        returnData['msg']='数据异常'+str(e)
    return jsonify(returnData)

@app.route('/spider',methods=['GET','POST'])
def spider():
    '''
        执行scrapyd
        :return:
        '''
    if request.method=='POST':
        returnData={'status':1,'msg':''}
        action=request.form.get('action')
        if action=='start':
            returnData['msg']=os.popen('curl http://spider.fzj.com:6800/schedule.json -d project=csdn -d spider=home').read()
        elif action=='stop':
            returnData['msg'] = os.popen('curl http://spider.fzj.com:6800/cancel.json -d project=csdn -d job={}'.format(request.form.get('job'))).read()
        elif action=='push':
            returnData['msg'] = os.popen('cd /vdb/python/csdn&&/vdc/anaconda3/bin/scrapyd-deploy demo -p csdn').read()
        elif action:
            job_list = json.loads(os.popen('curl http://spider.fzj.com:6800/listjobs.json?project=csdn').read())
            job_data=[{'status':'finished',**i} for i in job_list['finished']]+[{'status':'running',**i} for i in job_list['running']]+[{'status':'pending',**i} for i in job_list['pending']]
            returnData['code'] = 0
            returnData['count'] = len(job_data)
            returnData['data'] = job_data
            returnData['msg'] = ''
        else:
            print(action)
        return jsonify(returnData)
    versions_data=json.loads(os.popen('curl http://spider.fzj.com:6800/listversions.json?project=csdn').read())
    return render_template('run.html',versions_data=versions_data,versions_list=versions_data['versions'],versions_len=len(versions_data['versions']),title='爬虫操作')


if __name__ == '__main__':
    app.run(debug=True)